﻿using DBUtil;
using System;
using System.Collections.Generic;
using DotNetCommon.Extensions;
using System.Diagnostics;
using Dapper;
using System.Linq;
using System.Threading.Tasks;
using SqlSugar;
using MySql.Data.MySqlClient;

namespace PerformanceTest
{
    internal class Program
    {
        static string connString = "Server=127.0.0.1;Database=test;Uid=root;Pwd=123456;AllowLoadLocalInfile=true;SslMode=none;AllowPublicKeyRetrieval=True;Charset=utf8mb4;";
        static DBAccess db = DBFactory.CreateDB("MySql", connString);
        #region sql model
        static string createSql = @"create table test(id int primary key auto_increment,
name1 varchar(50),
name2 varchar(50),
name3 varchar(50),
name4 varchar(50),
name5 varchar(50),
name6 varchar(50),
name7 varchar(50),
name8 varchar(50),
name9 varchar(50),
name10 varchar(50),
name11 varchar(50),
name12 varchar(50),
name13 varchar(50),
name14 varchar(50),
name15 varchar(50),
name16 varchar(50),
name17 varchar(50),
name18 varchar(50),
name19 varchar(50),
name20 varchar(50),
name21 varchar(50),
name22 varchar(50),
name23 varchar(50),
name24 varchar(50),
name25 varchar(50),
name26 varchar(50),
name27 varchar(50),
name28 varchar(50),
name29 varchar(50),
name30 varchar(50),
name31 varchar(50),
name32 varchar(50),
name33 varchar(50),
name34 varchar(50),
name35 varchar(50),
name36 varchar(50),
name37 varchar(50),
name38 varchar(50),
name39 varchar(50),
name40 varchar(50),
name41 varchar(50),
name42 varchar(50),
name43 varchar(50),
name44 varchar(50),
name45 varchar(50),
name46 varchar(50),
name47 varchar(50),
name48 varchar(50),
name49 varchar(50))";

        public class Test
        {
            public int Id { get; set; }
            public string Name1 { get; set; }
            public string Name2 { get; set; }
            public string Name3 { get; set; }
            public string Name4 { get; set; }
            public string Name5 { get; set; }
            public string Name6 { get; set; }
            public string Name7 { get; set; }
            public string Name8 { get; set; }
            public string Name9 { get; set; }
            public string Name10 { get; set; }
            public string Name11 { get; set; }
            public string Name12 { get; set; }
            public string Name13 { get; set; }
            public string Name14 { get; set; }
            public string Name15 { get; set; }
            public string Name16 { get; set; }
            public string Name17 { get; set; }
            public string Name18 { get; set; }
            public string Name19 { get; set; }
            public string Name20 { get; set; }
            public string Name21 { get; set; }
            public string Name22 { get; set; }
            public string Name23 { get; set; }
            public string Name24 { get; set; }
            public string Name25 { get; set; }
            public string Name26 { get; set; }
            public string Name27 { get; set; }
            public string Name28 { get; set; }
            public string Name29 { get; set; }
            public string Name30 { get; set; }
            public string Name31 { get; set; }
            public string Name32 { get; set; }
            public string Name33 { get; set; }
            public string Name34 { get; set; }
            public string Name35 { get; set; }
            public string Name36 { get; set; }
            public string Name37 { get; set; }
            public string Name38 { get; set; }
            public string Name39 { get; set; }
            public string Name40 { get; set; }
            public string Name41 { get; set; }
            public string Name42 { get; set; }
            public string Name43 { get; set; }
            public string Name44 { get; set; }
            public string Name45 { get; set; }
            public string Name46 { get; set; }
            public string Name47 { get; set; }
            public string Name48 { get; set; }
            public string Name49 { get; set; }

        }
        #endregion

        static void Main(string[] args)
        {
            PrepareData();
            TestSelectDataTable().Wait();
            TestSelectModel();
            Console.WriteLine("over");
        }

        public static async Task TestSelectDataTable()
        {
            //预热
            var tmp = db.SelectDataTable("select * from test");
            var orm = new FreeSql.FreeSqlBuilder().UseConnectionString(FreeSql.DataType.MySql, connString).Build();
            tmp = orm.Ado.ExecuteDataTable("select * from test");
            Console.WriteLine("---------------------SelectDataTable------------------------------------------------------------");

            var list = new List<(int type, long total)>();

            //dbutil 同步
            var st = new Stopwatch();
            st.Start();
            for (int i = 0; i < 500; i++)
            {
                var dt = db.SelectDataTable("select * from test");
            }
            st.Stop();
            Console.WriteLine($"dbutil sync耗时: {st.ElapsedMilliseconds}/{st.ElapsedMilliseconds / 500.0} 毫秒");

            //dbutil 异步
            st = new Stopwatch();
            st.Start();
            for (int i = 0; i < 500; i++)
            {
                var dt = await db.SelectDataTableAsync("select * from test");
            }
            st.Stop();
            Console.WriteLine($"dbutil async耗时: {st.ElapsedMilliseconds}/{st.ElapsedMilliseconds / 500.0} 毫秒");

            //freesql 同步
            st = new Stopwatch();
            st.Start();
            for (int i = 0; i < 500; i++)
            {
                var dt = orm.Ado.ExecuteDataTable("select * from test");
            }
            st.Stop();
            Console.WriteLine($"freesql 同步耗时: {st.ElapsedMilliseconds}/{st.ElapsedMilliseconds / 500.0} 毫秒");

            //freesql 异步
            st = new Stopwatch();
            st.Start();
            for (int i = 0; i < 500; i++)
            {
                var dt = await orm.Ado.ExecuteDataTableAsync("select * from test");
            }
            st.Stop();
            Console.WriteLine($"freesql 异步耗时: {st.ElapsedMilliseconds}/{st.ElapsedMilliseconds / 500.0} 毫秒");
            Console.WriteLine();
        }

        public static void TestSelectModel()
        {
            Console.WriteLine("-------------------------SelectModelList--------------------------------------------------------");

            //预热
            var list = new List<(int type, long total)>();
            db.SelectModelList<Test>("select * from test");
            //dbutil读取
            var st = new Stopwatch();
            st.Start();
            for (int i = 0; i < 500; i++)
            {
                var models = db.SelectModelList<Test>("select * from test");
            }
            st.Stop();
            Console.WriteLine($"dbutil耗时: {st.ElapsedMilliseconds}/{st.ElapsedMilliseconds / 500.0} 毫秒");

            //freesql读取
            var orm = new FreeSql.FreeSqlBuilder().UseConnectionString(FreeSql.DataType.MySql, connString).Build();
            orm.Select<Test>().ToList();
            st = new Stopwatch();
            st.Start();
            for (int i = 0; i < 500; i++)
            {
                var models = orm.Select<Test>().ToList();
            }
            st.Stop();
            Console.WriteLine($"freesql 耗时: {st.ElapsedMilliseconds}/{st.ElapsedMilliseconds / 500.0} 毫秒");

            //dapper
            using (var conn = new MySqlConnection(connString))
            {
                var models = conn.Query<Test>("select * from test").ToList();
            }
            st = new Stopwatch();
            st.Start(); using (var conn = new MySqlConnection(connString))
            {
                for (int i = 0; i < 500; i++)
                {

                    var models = conn.Query<Test>("select * from test").ToList();
                }
            }
            st.Stop();
            Console.WriteLine($"dapper耗时: {st.ElapsedMilliseconds}/{st.ElapsedMilliseconds / 500.0} 毫秒");

            //SqlSugar
            var SqlSugar = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = connString,//连接符字串
                DbType = DbType.MySql, //数据库类型
                //IsAutoCloseConnection = true //不设成true要手动close
            });
            SqlSugar.Queryable<Test>().ToList();
            st = new Stopwatch();
            st.Start(); using (var conn = new MySqlConnection(connString))
            {
                for (int i = 0; i < 500; i++)
                {

                    var models = SqlSugar.Queryable<Test>().ToList();
                }
            }
            st.Stop();
            Console.WriteLine($"sqlsugar耗时: {st.ElapsedMilliseconds}/{st.ElapsedMilliseconds / 500.0} 毫秒");

            //原生
            st = new Stopwatch();
            st.Start();
            for (int i = 0; i < 500; i++)
            {
                var models = db.SelectDataReader<List<Test>>(reader =>
                {
                    var models = new List<Test>();
                    while (reader.RawReader.Read())
                    {
                        models.Add(new Test
                        {
                            Id = reader.RawReader.GetInt32(0),
                            #region 手动赋值
                            Name1 = reader.RawReader.GetString(1),
                            Name2 = reader.RawReader.GetString(2),
                            Name3 = reader.RawReader.GetString(3),
                            Name4 = reader.RawReader.GetString(4),
                            Name5 = reader.RawReader.GetString(5),
                            Name6 = reader.RawReader.GetString(6),
                            Name7 = reader.RawReader.GetString(7),
                            Name8 = reader.RawReader.GetString(8),
                            Name9 = reader.RawReader.GetString(9),
                            Name10 = reader.RawReader.GetString(10),
                            Name11 = reader.RawReader.GetString(11),
                            Name12 = reader.RawReader.GetString(12),
                            Name13 = reader.RawReader.GetString(13),
                            Name14 = reader.RawReader.GetString(14),
                            Name15 = reader.RawReader.GetString(15),
                            Name16 = reader.RawReader.GetString(16),
                            Name17 = reader.RawReader.GetString(17),
                            Name18 = reader.RawReader.GetString(18),
                            Name19 = reader.RawReader.GetString(19),
                            Name20 = reader.RawReader.GetString(20),
                            Name21 = reader.RawReader.GetString(21),
                            Name22 = reader.RawReader.GetString(22),
                            Name23 = reader.RawReader.GetString(23),
                            Name24 = reader.RawReader.GetString(24),
                            Name25 = reader.RawReader.GetString(25),
                            Name26 = reader.RawReader.GetString(26),
                            Name27 = reader.RawReader.GetString(27),
                            Name28 = reader.RawReader.GetString(28),
                            Name29 = reader.RawReader.GetString(29),
                            Name30 = reader.RawReader.GetString(30),
                            Name31 = reader.RawReader.GetString(31),
                            Name32 = reader.RawReader.GetString(32),
                            Name33 = reader.RawReader.GetString(33),
                            Name34 = reader.RawReader.GetString(34),
                            Name35 = reader.RawReader.GetString(35),
                            Name36 = reader.RawReader.GetString(36),
                            Name37 = reader.RawReader.GetString(37),
                            Name38 = reader.RawReader.GetString(38),
                            Name39 = reader.RawReader.GetString(39),
                            Name40 = reader.RawReader.GetString(40),
                            Name41 = reader.RawReader.GetString(41),
                            Name42 = reader.RawReader.GetString(42),
                            Name43 = reader.RawReader.GetString(43),
                            Name44 = reader.RawReader.GetString(44),
                            Name45 = reader.RawReader.GetString(45),
                            Name46 = reader.RawReader.GetString(46),
                            Name47 = reader.RawReader.GetString(47),
                            Name48 = reader.RawReader.GetString(48),
                            Name49 = reader.RawReader.GetString(49),
                            #endregion
                        });
                    }
                    return models;
                }, "select * from test");
            }
            st.Stop();
            Console.WriteLine($"原生耗时: {st.ElapsedMilliseconds}/{st.ElapsedMilliseconds / 500.0} 毫秒");

            Console.WriteLine();
        }

        static void PrepareData()
        {
            #region 准备数据
            db.ExecuteSql(db.Manage.DropTableIfExistSql("test"));
            db.ExecuteSql(createSql);

            var dics = new List<Dictionary<string, object>>();
            for (var i = 0; i < 10000; i++)
            {
                var dic = new Test
                {
                    #region 属性赋值
                    Name1 = "Name1",
                    Name2 = "Name2",
                    Name3 = "Name3",
                    Name4 = "Name4",
                    Name5 = "Name5",
                    Name6 = "Name6",
                    Name7 = "Name7",
                    Name8 = "Name8",
                    Name9 = "Name9",
                    Name10 = "Name10",
                    Name11 = "Name11",
                    Name12 = "Name12",
                    Name13 = "Name13",
                    Name14 = "Name14",
                    Name15 = "Name15",
                    Name16 = "Name16",
                    Name17 = "Name17",
                    Name18 = "Name18",
                    Name19 = "Name19",
                    Name20 = "Name20",
                    Name21 = "Name21",
                    Name22 = "Name22",
                    Name23 = "Name23",
                    Name24 = "Name24",
                    Name25 = "Name25",
                    Name26 = "Name26",
                    Name27 = "Name27",
                    Name28 = "Name28",
                    Name29 = "Name29",
                    Name30 = "Name30",
                    Name31 = "Name31",
                    Name32 = "Name32",
                    Name33 = "Name33",
                    Name34 = "Name34",
                    Name35 = "Name35",
                    Name36 = "Name36",
                    Name37 = "Name37",
                    Name38 = "Name38",
                    Name39 = "Name39",
                    Name40 = "Name40",
                    Name41 = "Name41",
                    Name42 = "Name42",
                    Name43 = "Name43",
                    Name44 = "Name44",
                    Name45 = "Name45",
                    Name46 = "Name46",
                    Name47 = "Name47",
                    Name48 = "Name48",
                    Name49 = "Name49",
                    #endregion
                };
                dics.Add(dic.ToDictionary().RemoveFluent("Id"));
            }
            db.Insert("test", dics.ToArray());
            Console.WriteLine($"插入完成,开始比对：");
            #endregion
        }
    }
}
